package com.feiren.o2o.rest.plsql;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.json.JSONArray;
import org.json.JSONObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import oracle.jdbc.internal.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;

@RestController
class PlsqlController {

	private Logger logger = LoggerFactory.getLogger(PlsqlController.class);

	@Autowired
	JdbcTemplate jdbcTemplate;

	@SuppressWarnings("unchecked")
	@RequestMapping(value = "/plsql/procedures", method = RequestMethod.POST)
	public String get(@RequestParam(value = "name") String name, @RequestParam(value = "inParams") String inParams,
			@RequestParam(value = "outputTypes") String outputTypes) {
		/**
		 * OracleTypes.NUMBER:2 OracleTypes.CURSOR:-10 OracleTypes.VARCHAR:12
		 */
		JSONArray paramsJa = new JSONArray(inParams);

		JSONArray outputTypesJa = new JSONArray(outputTypes);
		String storedProc = parseStatement(name, paramsJa, outputTypesJa);
		jdbcTemplate.execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(Connection con) throws SQLException {
				CallableStatement cs = con.prepareCall(storedProc);
				for (int i = 1; i <= paramsJa.length(); i++) {
					JSONObject paramJo = paramsJa.getJSONObject(i - 1);
					int paramType = paramJo.getInt("type");
					Object paramValue = paramJo.get("value");
					if (paramType == OracleTypes.ARRAY) {
						String objDef = paramJo.getString("class");
						String arrayDef = paramJo.getString("array");
						JSONArray datas = (JSONArray) paramValue;
						JSONArray fields = paramJo.getJSONArray("fields");
						StructDescriptor recDesc = StructDescriptor.createDescriptor(objDef,
								con.getMetaData().getConnection());
						List<STRUCT> pstruct = new ArrayList<STRUCT>();
						for (int j = 0; j < datas.length(); j++) {
							JSONObject data = datas.getJSONObject(j);
							Object[] record = new Object[fields.length()];
							for (int k = 0; k < fields.length(); k++) {
								String field = fields.getString(k);
								Object obj = data.get(field);
								record[k] = obj;
							}
							STRUCT item = new STRUCT(recDesc, con.getMetaData().getConnection(), record);
							pstruct.add(item);
						}
						ArrayDescriptor tabDesc = ArrayDescriptor.createDescriptor(arrayDef,
								con.getMetaData().getConnection());
						ARRAY vArray = new ARRAY(tabDesc, con.getMetaData().getConnection(), pstruct.toArray());
						cs.setArray(i, vArray);
					} else if (paramType == OracleTypes.NUMBER) {
						if (paramValue instanceof Integer) {
							cs.setDouble(i, (Integer) paramValue);
						} else if (paramValue instanceof Float) {
							cs.setDouble(i, (Float) paramValue);
						} else {
							cs.setDouble(i, (Double) paramValue);
						}

					} else {
						cs.setString(i, paramValue.toString());
					}
				}
				for (int i = paramsJa.length() + 1; i <= outputTypesJa.length() + paramsJa.length(); i++) {
					cs.registerOutParameter(i, outputTypesJa.getInt(i - paramsJa.length() - 1));
				}
				return cs;
			}
		}, new CallableStatementCallback() {
			public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
				cs.execute();
				for (int i = 1; i <= outputTypesJa.length(); i++) {
					logger.trace(cs.getObject(i).getClass().toString());
				}

				System.out.println("p_Resultcs:" + cs.getString(2));
				System.out.println("p_Retcode:" + cs.getString(3));
				System.out.println("p_Errmsg:" + cs.getString(4));
				// ResultSet rs = (ResultSet) cs.getObject(6);
				// while (rs.next()) {
				// System.out.println(rs.getString("ename"));
				// }
				// for (int i = 0; i < outputTypesJa.length(); i++) {
				// int outputType = outputTypesJa.getInt(i);
				// }
				return cs.getString(2);
			}
		});
		return "1";

		// return "index";
	}

	private String parseStatement(String id, JSONArray paramsJa, JSONArray outputTypesJa) {
		int length = paramsJa.length() + outputTypesJa.length();
		StringBuffer sb = new StringBuffer("{call " + id + "(");
		for (int i = 0; i < length; i++) {
			if (i == length - 1) {
				sb.append("?");
			} else {
				sb.append("?,");
			}
		}
		sb.append(")}");
		return sb.toString();
	}

}
